<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> 
<html>
<head>
<title>Working with images</title>
<link rel="stylesheet" href="/cfg/format.css" type="text/css">
<meta http-equiv="content-type" content="text/html; charset=utf-8">
<meta name="keywords" content="SQLite, Perl, DBI, images, tutorial, database, 
programming, development">
<meta name="description" content="In this part of the SQLite Perl tutorial, 
we work with images. We insert an image to the database and retrieve it back. ">
<meta name="language" content="en">
<meta name="author" content="Jan Bodnar">
<meta name="distribution" content="global">

<script type="text/javascript" src="/lib/jquery.js"></script>
<script type="text/javascript" src="/lib/common.js"></script>

</head>

<body>

<div class="container">

<div id="wide_ad" class="ltow">
<script type="text/javascript"><!--
google_ad_client = "pub-9706709751191532";
/* 160x600, August 2011 */
google_ad_slot = "2484182563";
google_ad_width = 160;
google_ad_height = 600;
//-->
</script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</div>

<div class="content">


<a href="/" title="Home">Home</a>&nbsp;
<a href="..">Contents</a>



<h1>Working with images</h1>

<p>
In this chapter of the SQLite Perl tutorial, we will work with image files.
Note that some people argue against putting  images into databases. Here we 
only show how to do it. We do not dwell into technical issues of whether to save images in
databases or not. 
</p>

<pre>
sqlite> CREATE TABLE Images(Id INTEGER PRIMARY KEY, Data BLOB);
</pre>

<p>
For this example, we create a new table called Images. For the images, we use
the <code>BLOB</code> data type, which stands for Binary Large Object. 
</p>

<div class="center">
<script type="text/javascript"><!--
google_ad_client = "ca-pub-9706709751191532";
/* top_horizontal */
google_ad_slot = "3327173442";
google_ad_width = 468;
google_ad_height = 60;
//-->
</script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</div>


<h2>Inserting images</h2>

<p>
In the first example, we are going to insert an image to the 
SQLite database. 
</p>

<pre class="code">
#!/usr/bin/perl

use strict;
use DBI;

my $dbh = DBI->connect(          
    "dbi:SQLite:dbname=test.db",                         
    { RaiseError => 1 }
) or die $DBI::errstr;

open IMAGE, "mushrooms.jpg" or die $!;

my ($image, $buff);
while(read IMAGE, $buff, 1024) {
    $image .= $buff;
}

my $stm = $dbh->prepare("INSERT INTO Images(Data) VALUES (?)");
$stm->bind_param(1, $image, DBI::SQL_BLOB);
$stm->execute();

close(IMAGE);
$stm->finish();
$dbh->disconnect();
</pre>

<p>
We read an image from the current working directory 
and write it into the Images table of the SQLite test.db database. 
</p>

<pre class="explanation">
open IMAGE, "mushrooms.jpg" or die $!;
</pre>

<p>
We open an image. It is a jpg image called mushrooms.jpg.
</p>

<pre class="explanation">
my ($image, $buff);
while(read IMAGE, $buff, 1024) {
    $image .= $buff;
}
</pre>

<p>
We read binary data from the image file. 
</p>

<pre class="explanation">
my $sth = $dbh->prepare("INSERT INTO Images(Data) VALUES (?)");
$sth->bind_param(1, $image, DBI::SQL_BLOB);
$sth->execute();
</pre>

<p>
The three code lines prepare the SQL statement, bind the image
data to the statement and execute it. 
</p>

<pre class="explanation">
close(IMAGE);
$sth->finish();
$dbh->disconnect();
</pre>

<p>
Finally, we are releasing the resources. 
</p>


<h2>Reading images</h2>

<p>
In this section, we are going to perform the reverse operation.
We will read an image from the database table. 
</p>

<pre class="code">
#!/usr/bin/perl

use strict;
use DBI;

my $dbh = DBI->connect(          
    "dbi:SQLite:dbname=test.db",                    
    { RaiseError => 1 }
) or die $DBI::errstr;

my $stm = $dbh->prepare("SELECT Data FROM Images WHERE Id=1");
$stm->execute();
my $image = $stm->fetch();

print "Image: $image";

open IMAGE, ">mushrooms2.jpg" or die $!;
print IMAGE @$image;
close(IMAGE);

$stm->finish();
$dbh->disconnect();
</pre>

<p>
We read image data from the Images table and write it
to another file, which we call mushrooms2.jpg. 
</p>

<pre class="explanation">
my $sth = $dbh->prepare("SELECT Data FROM Images WHERE Id=1");
$sth->execute();
my $image = $sth->fetch();
</pre>

<p>
These three lines select the image data from the table.
</p>

<pre class="explanation">
open IMAGE, ">mushrooms2.jpg" or die $!;
print IMAGE @$image;
close(IMAGE);
</pre>

<p>
We open a new image file and write the retrieved data
into that file. Then we close the file.  
</p>

<p>
This part of the SQLite Perl tutorial was dedicated to reading and writing
images. 
</p>


<div class="botNav, center">
<span class="botNavItem"><a href="/">Home</a></span> ‡ <span class="botNavItem"><a href="..">Contents</a></span> ‡ 
<span class="botNavItem"><a href="#">Top of Page</a></span>
</div>


<div class="footer">
<div class="signature">
<a href="/">ZetCode</a> last modified April 3, 2012  <span class="copyright">&copy; 2007 - 2013 Jan Bodnar</span>
</div>
</div>

</div> <!-- content -->

</div> <!-- container -->

</body>
</html>

